package com.tool;

import com.core.jdbc.SqlHelper;
import com.core.netty.http.work.HttpMyReponse;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelTool {

    public static void main(String[] args) throws Exception {
        String sql = "select custid,nickname,telphone from cust limit 5 ";

        List<Map<String, Object>> list = SqlHelper.queryMapList(sql, new HashMap<>());
        System.out.println(list);
        outExcel(list);

    }

    public static void outExcelRep(HttpMyReponse rep, List<Map<String, Object>> list) throws IOException {
        //1.在内存中创建一个excel文件
        XSSFWorkbook hssfWorkbook = new XSSFWorkbook();
        //2.创建工作簿
        XSSFSheet sheet = hssfWorkbook.createSheet();
        //3.创建标题行
        XSSFRow titlerRow = sheet.createRow(0);
        titlerRow.createCell(0).setCellValue("列1");
        titlerRow.createCell(1).setCellValue("列2");
        titlerRow.createCell(2).setCellValue("列3");

        //4.遍历数据,创建数据行
        for (Map<String, Object> map : list) {
            //获取最后一行的行号
            int lastRowNum = sheet.getLastRowNum();
            XSSFRow dataRow = sheet.createRow(lastRowNum + 1);
            dataRow.createCell(0).setCellValue(map.get("custid").toString());
            dataRow.createCell(1).setCellValue(map.get("nickname").toString());
            dataRow.createCell(2).setCellValue(map.get("telphone").toString());
        }
        //5.创建文件名
        String fileName = "用户信息.xls";


        //9.设置信息头
        rep.setHeader("content-ype", "video/x-flic");
        rep.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        //10.写出文件,关闭流
        //hssfWorkbook.write(outputStream);
        hssfWorkbook.close();
    }


    public static void outExcel(List<Map<String, Object>> list) throws IOException {
        //1.在内存中创建一个excel文件
        XSSFWorkbook hssfWorkbook = new XSSFWorkbook();
        //2.创建工作簿
        XSSFSheet sheet = hssfWorkbook.createSheet();
        //3.创建标题行
        XSSFRow titlerRow = sheet.createRow(0);
        titlerRow.createCell(0).setCellValue("列1");
        titlerRow.createCell(1).setCellValue("列2");
        titlerRow.createCell(2).setCellValue("列3");

        //4.遍历数据,创建数据行
        for (Map<String, Object> map : list) {
            //获取最后一行的行号
            int lastRowNum = sheet.getLastRowNum();
            XSSFRow dataRow = sheet.createRow(lastRowNum + 1);
            dataRow.createCell(0).setCellValue(map.get("custid").toString());
            dataRow.createCell(1).setCellValue(map.get("nickname").toString());
            dataRow.createCell(2).setCellValue(map.get("telphone").toString());
        }
        //5.创建文件名
        String fileName = "用户信息.xls";
        //6.获取输出流对象
        File f = new File("E:/testabc/jinjiao/abcde.xlsx");
//        if (!f.exists()) {
//            f.createNewFile();
//        }
        OutputStream os = new FileOutputStream(f);
        //10.写出文件,关闭流
        hssfWorkbook.write(os);
        hssfWorkbook.close();
        os.close();
    }


}
